/**
 * Project: ld.biz.service
 * 
 * File Created at 2012-9-23
 * $Id$
 * 
 * Copyright 1999-2100 Alibaba.com Corporation Limited.
 * All rights reserved.
 *
 * This software is the confidential and proprietary information of
 * Alibaba Company. ("Confidential Information").  You shall not
 * disclose such Confidential Information and shall use it only in
 * accordance with the terms of the license agreement you entered into
 * with Alibaba.com.
 */
package com.liangdev.biz.core.autocode;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.liangdev.biz.entity.autocode.FieldMetaData;
import com.liangdev.biz.entity.autocode.TableMetaData;

/**
 * TODO Comment of OracleDBMetaDataCollector
 * 
 * @author chongan.wangca
 */
public class OracleDBMetaDataCollector implements DBMetaDataCollector {

    //    private final Logger logger = LoggerFactory.getLogger(OracleDBMetaDataCollector.class);

    /**
     * @param ip
     * @param port
     * @param dbName
     * @param user
     * @param password
     * @return
     * @throws Exception
     */
    private Connection getConnection(String ip, String port, String dbName, String user,
                                     String password) throws Exception {
        Class.forName("oracle.jdbc.driver.OracleDriver");
        Connection connection = DriverManager.getConnection("jdbc:oracle:thin:@" + ip.trim() + ":"
                + port.trim() + ":" + dbName, user, password);
        return connection;
    }

    /*
     * (non-Javadoc)
     * @see
     * com.liangdev.biz.core.autocode.DBMetaDataCollector#getAllTables(java.
     * lang.String, java.lang.String, java.lang.String, java.lang.String)
     */
    @Override
    public List<TableMetaData> getAllTables(String ip, String port, String dbName, String user,
                                            String password) throws Exception {

        List<TableMetaData> tableMetaDatas = new ArrayList<TableMetaData>();
        Connection connection = getConnection(ip, port, dbName, user, password);
        ResultSet rs = null;
        try {
            DatabaseMetaData meta = connection.getMetaData();
            rs = meta.getTables(null, null, null, new String[] { "TABLE" });
            while (rs.next()) {
                //表所属用户名
                String tableUser = rs.getString(2);
                //表名
                String tableName = rs.getString(3);
                if (user.equalsIgnoreCase(tableUser)) {
                    TableMetaData tableMetaData = new TableMetaData();
                    tableMetaData.setDbType("ORACLE");
                    tableMetaData.setDbTableName(tableName);
                    tableMetaDatas.add(tableMetaData);
                }
            }
            return tableMetaDatas;
        } finally {
            if (rs != null) {
                rs.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    /*
     * (non-Javadoc)
     * @see
     * com.liangdev.biz.core.autocode.DBMetaDataCollector#getTable(java.lang
     * .String, java.lang.String, java.lang.String, java.lang.String)
     */
    @Override
    public TableMetaData getTable(String tableName, String ip, String port, String dbName,
                                  String user, String password) throws Exception {

        Connection connection = getConnection(ip, port, dbName, user, password);
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        try {
            pstmt = (PreparedStatement) connection.prepareStatement(getQueryTableInfoSQL());
            pstmt.setString(1, tableName);
            //这点特别要注意:如果是Oracle.而对于mysql可以不用加
            pstmt.execute();
            rs = pstmt.getResultSet();

            List<FieldMetaData> fieldMetaDatas = new ArrayList<FieldMetaData>();

            while (rs.next()) {
                FieldMetaData fieldMetaData = new FieldMetaData();
                fieldMetaData.setDbFiledName(rs.getString(2));
                fieldMetaData.setDbFieldType(rs.getString(3));
                fieldMetaData.setComment(rs.getString(6));
                fieldMetaDatas.add(fieldMetaData);
            }

            TableMetaData tableMetaData = new TableMetaData();
            tableMetaData.setDbTableName(tableName);
            tableMetaData.setDbType("ORACLE");
            tableMetaData.setFieldMetaDatas(fieldMetaDatas);

            return tableMetaData;

        } finally {
            if (rs != null) {
                rs.close();
            }
            if (pstmt != null) {
                pstmt.close();
            }
            if (connection != null) {
                connection.close();
            }
        }
    }

    private String getQueryTableInfoSQL() {
        return " SELECT USER_TAB_COLS.TABLE_NAME,                                  "
                + " USER_TAB_COLS.COLUMN_NAME,                                     "
                + " USER_TAB_COLS.DATA_TYPE,                                       "
                + " USER_TAB_COLS.DATA_LENGTH,                                     "
                + " USER_TAB_COLS.NULLABLE,                                        "
                + " user_col_comments.comments                                     "
                + " FROM USER_TAB_COLS                                             "
                + " inner join user_col_comments                                   "
                + " on user_col_comments.TABLE_NAME = USER_TAB_COLS.TABLE_NAME     "
                + " and user_col_comments.COLUMN_NAME = USER_TAB_COLS.COLUMN_NAME  "
                + " and USER_TAB_COLS.TABLE_NAME = ?                               ";
    }
}
